const moment = require('moment')
const { mysqlPool } = require('../config/db')

class User {
	// 根据用户名查找用户信息
	static async findByUsername(username) {
		const [rows] = await mysqlPool.execute(
			'SELECT id, username, password FROM users WHERE username = ?',
			[username]
		)

		return rows[0]
	}

    // 根据用户id查找用户信息
    static async findByUserId(id) {
        // 查询当前用户信息以及这个用户所写的文章数量
        let sql = `
            SELECT
                u.id,
                u.username,
                u.nickName,
                u.job,
                u.companyName,
                u.avatar,
                u.introduction,
                u.email,
                u.sex,
                u.phone,
                u.state,
                u.is_bind_mobile AS isBindMobile,
                DATE_FORMAT(u.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                COUNT(a.id) AS articleCount
            FROM users u
            LEFT JOIN article a ON a.userId = u.id
            WHERE u.id = ?
            GROUP BY u.id
        `

		const [rows] = await mysqlPool.execute(sql, [id])

		return rows[0]
	}

    // 登录
    static async login(id) {
        const [result] = await mysqlPool.execute(
            `UPDATE users SET state = 1, del_time = NULL, last_login_time = ? WHERE id = ?`,
            [moment().format('YYYY-MM-DD HH:mm:ss'), id]
        )

        return result
    }

	// 创建新用户（注册功能）
	static async create(userData) {
		const { username, password } = userData
        const createTime = moment().format('YYYY-MM-DD HH:mm:ss')
		const [result] = await mysqlPool.execute(
			'INSERT INTO users (username, password, createTime) VALUES (?, ?, ?)',
			[username, password, createTime]
		)

		return result.insertId
	}

    // 更新用户信息
    static async updateUserInfo(data) {
        const { id, nickName, avatar, sex, email, job, companyName, introduction } = data
        
        let sql = `UPDATE users SET `
        let params = []

        if (nickName) {
            sql += `nickName = ?, `
            params.push(nickName)
        }

        if (avatar) {
            sql += `avatar = ?, `
            params.push(avatar)
        }

        if (sex) {
            sql += `sex = ?, `
            params.push(sex)
        }

        if (job) {
            sql += `job = ?, `
            params.push(job)
        }

        if (companyName) {
            sql += `companyName = ?, `
            params.push(companyName)
        }

        if (introduction) {
            sql += `introduction = ?, `
            params.push(introduction)
        }

        if (email) {
            sql += `email = ?, `
            params.push(email)
        }

        sql = sql.slice(0, -2)
        sql += ` WHERE id = ?`
        params.push(id)

        const [reulst] = await mysqlPool.execute(sql, params)

        return reulst
    }

    // 关注用户
    static async follow(userId, followId) {
        // 判断是否已经关注过
        const [rows] = await mysqlPool.execute(
            'SELECT * FROM follow WHERE userId = ? AND followId = ?',
            [userId, followId]
        )

        if (rows.length > 0) {
            return {
                code: 500,
                data: null,
                msg: '已经关注过该用户！'
            }
        }

        // 插入关注关系
        const [result] = await mysqlPool.execute(
            'INSERT INTO follow (userId, followId) VALUES (?, ?)',
            [userId, followId]
        )

        return result
    }

    // 取消关注
    static async cancelFollow(userId, followId) {
        // 判断是否已经关注过
        const [rows] = await mysqlPool.execute(
            'SELECT * FROM follow WHERE userId = ? AND followId = ?',
            [userId, followId]
        )

        if (!rows || rows.length === 0) {
            return {
                code: 500,
                data: null,
                msg: '您还未关注此博主！'
            }
        }

        // 删除关注关系
        const [result] = await mysqlPool.execute(
            'DELETE FROM follow WHERE userId = ? AND followId = ?',
            [userId, followId]
        )

        return result
    }

    // 注销账号
    // 0 禁用/拉黑，1 正常，2 等待删除（等待注销）
    static async delAccount(userId) {
        const del_time = moment().format('YYYY-MM-DD HH:mm:ss')
        const sql = `UPDATE users SET state = 2, del_time = ? WHERE id = ?`
        const [result] = await mysqlPool.execute(sql, [del_time, userId])
        return result
    }

    // 重置密码
    static async resetPassword(userId, password) {
        const sql = `UPDATE users SET password = ? WHERE id = ?`
        const [result] = await mysqlPool.execute(sql, [password, userId])
        return result
    }

    // 绑定手机号
    static async bindPhone(userId, phone) {
        // 匹配以 1 开头，第二位为 3-9 的数字，后面跟着 9 个数字的正则
        const reg = /^1[3-9]\d{9}$/g
        if (!phone || !reg.test(phone)) {
            return { code: 500, data: null, msg: '手机号格式错误！' }
        }

        const [result] = await mysqlPool.execute(
            `UPDATE users SET phone = ?, is_bind_mobile = 1 WHERE id = ?`,
            [phone, userId]
        )

        return result
    }

    // 查询用户是否关注过文章作者
    static async findFollowState(followId, userId) {
        // 查询用户是否关注过文章作者
        const [result] = await mysqlPool.execute(
            'SELECT COUNT(*) AS total FROM follow WHERE userId = ? AND followId = ?',
            [userId, followId]
        )

        if (result && result[0].total > 0) {
            return { code: 200, data: true, msg: '此作者已关注！' }
        } else {
            return { code: 200, data: false, msg: '此作者未关注！' }
        }
    }

    // 查询聊天记录
    static async getChatRecord(userId, recipientId, currentPage, pageSize) {
        const sql1 = `SELECT COUNT(*) AS total FROM chat c WHERE (c.sender_id = ? AND c.recipient_id = ?) OR (c.sender_id = ? AND c.recipient_id = ?)`
        const [result1] = await mysqlPool.execute(sql1, [userId, recipientId, recipientId, userId])

        if (!result1) {
            return { code: 500, data: null, msg: '聊天记录不存在！' }
        }

        if (result1[0].total === 0) {
            return { code: 200, data: { total: 0, rows: [] }, msg: '暂无聊天记录！' }
        }

        // 查询聊天记录以及发送者信息
        const sql2 = `
            SELECT
                c.id AS chatId,
                c.sender_id AS senderId,
                c.recipient_id AS recipientId,
                c.content,
                c.type,
                c.state,
                c.is_revoke AS isRevoke,
                DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                u.username AS senderUsername,
                u.nickName AS senderNickName,
                u.avatar AS senderAvatar
            FROM
                chat c
            LEFT JOIN users u ON c.sender_id = u.id
            WHERE
                (c.sender_id = ? AND c.recipient_id = ?)
                OR (c.sender_id = ? AND c.recipient_id = ?)
            ORDER BY
            c.createTime DESC
            LIMIT ${(currentPage - 1) * pageSize}, ${pageSize}
        `
        const [result2] = await mysqlPool.execute(sql2, [userId, recipientId, recipientId, userId])
        return { code: 200, data: { total: result1[0].total, rows: result2 }, msg: '查询成功！' }
    }

    // 将聊天内容发送超过 1 分钟的消息置为不可撤回，is_revoke = 1
    static async updateRevokeState() {
        const sql = `UPDATE chat SET is_revoke = 1 WHERE createTime < DATE_SUB(NOW(), INTERVAL 1 MINUTE)`
        const [result] = await mysqlPool.execute(sql)

        if (result && result.affectedRows > 0) {
            return { code: 200, data: null, msg: '修改成功！' }
        }

        return { code: 500, data: null, msg: '修改失败！' }
    }

    // 获取用户未读聊天消息列表
    static async getNoReadMsgList(userId) {
        const sql = `
            SELECT
                c.id AS chatId,
                c.sender_id AS senderId,
                c.recipient_id AS recipientId,
                c.content,
                c.type,
                c.state,
                c.is_revoke AS isRevoke,
                DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                u.username AS senderUsername,
                u.nickName AS senderNickName,
                u.avatar AS senderAvatar,
                u.job AS senderJob,
                u.state AS senderState,
                u.sex AS senderSex
            FROM
                chat c
            LEFT JOIN users u ON c.sender_id = u.id
            WHERE
                c.recipient_id = ?
                AND c.state = 0
            ORDER BY
                c.createTime DESC
        `
        const [result] = await mysqlPool.execute(sql, [userId])

        return result
    }

    // 获取用户未读消息数量
    static async getNoReadMsgCount(userId) {
        const sql = `SELECT COUNT(*) AS total FROM chat WHERE recipient_id = ? AND state = 0`
        const [result] = await mysqlPool.execute(sql, [userId])

        return result[0]
    }

    // 批量修改用户消息状态（将未读设为已读）
    static async updateMsgState(userId, msgIds) {
        const sql = `UPDATE chat SET state = 1 WHERE recipient_id = ? AND id IN (${msgIds})`
        const [result] = await mysqlPool.execute(sql, [userId])

        if (result && result.affectedRows > 0) {
            return { code: 200, data: null, msg: '修改成功！' }
        }

        return { code: 500, data: null, msg: '修改失败！' }
    }

    // 将好友发送的消息，设为已读
    static async updateFriendMsgState(senderId, userId) {
        const sql = `UPDATE chat SET state = 1 WHERE sender_id = ? AND recipient_id = ? AND state = 0`
        const [result] = await mysqlPool.execute(sql, [senderId, userId])

        if (result) {
            if (result.affectedRows > 0) {
                return { code: 200, data: null, msg: '修改成功！' }
            } else {
                return { code: 200, data: null, msg: '该好友没有未读消息！' }
            }
        }

        return { code: 500, data: null, msg: '修改失败！' }
    }

    // 撤回消息
    static async revokeMsg(userId, msgId) {
        const sql1 = `SELECT * FROM chat WHERE id = ? AND sender_id = ?`
        const [result1] = await mysqlPool.execute(sql1, [msgId, userId])

        if (!result1) {
            return { code: 500, data: null, msg: '消息不存在！' }
        }

        const sql = `DELETE FROM chat WHERE id = ?`
        const [result] = await mysqlPool.execute(sql, [msgId])

        if (result && result.affectedRows > 0) {
            return { code: 200, data: null, msg: '撤回成功！' }
        }

        if (result && result.affectedRows === 0) {
            return { code: 500, data: null, msg: '消息不存在或已撤回！' }
        }

        return { code: 500, data: null, msg: '撤回异常，请联系管理员！' }
    }

    // 查询好友（互相关注）列表
    static async getFriendList(userId) {
        const sql = `
            SELECT 
                u.id AS userId,
                u.username,
                u.nickName,
                u.avatar,
                c.content AS lastMessage,
                DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS lastMessageTime,
                c.sender_id AS lastMessageSender,
                (c.sender_id = ?) AS isOwnLastMessage,
                c.state AS isReadState,
                c.type AS lastMessageType,
                COALESCE(unread.noReadCount, 0) AS noReadCount
            FROM users u
            INNER JOIN follow f1 ON u.id = f1.followId AND f1.userId = ?
            INNER JOIN follow f2 ON f1.userId = f2.followId AND f1.followId = f2.userId
            LEFT JOIN (
                SELECT c1.*
                FROM chat c1
                INNER JOIN (
                        SELECT 
                            LEAST(sender_id, recipient_id) AS user1,
                            GREATEST(sender_id, recipient_id) AS user2,
                            MAX(createTime) AS maxTime
                        FROM chat c
                        WHERE (sender_id = ? OR recipient_id = ?) 
                        GROUP BY LEAST(sender_id, recipient_id), GREATEST(sender_id, recipient_id)
                ) c2 ON LEAST(c1.sender_id, c1.recipient_id) = c2.user1 
                        AND GREATEST(c1.sender_id, c1.recipient_id) = c2.user2 
                        AND c1.createTime = c2.maxTime
            ) c ON (c.sender_id = ? AND c.recipient_id = u.id) 
                    OR (c.sender_id = u.id AND c.recipient_id = ?)
            LEFT JOIN (
                SELECT 
                    sender_id,
                    COUNT(*) AS noReadCount
                FROM chat 
                WHERE recipient_id = ?
                    AND state = 0
                GROUP BY sender_id
            ) unread ON unread.sender_id = u.id
            WHERE u.state = 1
            ORDER BY c.createTime DESC
        `
        const [result] = await mysqlPool.execute(sql, [userId, userId, userId, userId, userId, userId, userId])

        if (result && result.length > 0) {
            return { code: 200, data: result, msg: '查询成功！' }
        }

        if (result && result.length === 0) {
            return { code: 200, data: [], msg: '暂无好友！' }
        }

        return { code: 500, data: null, msg: '查询异常，请联系管理员！' }
    }
}

module.exports = User
